import pytz
from datetime import date, timedelta, datetime
from dateutil.relativedelta import relativedelta

from odoo import models, api, fields
from odoo.tools import DEFAULT_SERVER_DATETIME_FORMAT, DEFAULT_SERVER_DATE_FORMAT


class ProductionDailyReport(models.AbstractModel):
    _name = 's.production.daily.report.two'
    _description = 'Production Daily Report'

    @api.model
    def get_production_daily_data(self, s_date=None, date_type='week'):
        utc_now = datetime.utcnow()
        target_tz = pytz.timezone(self._context.get('tz'))
        local_time = utc_now.replace(tzinfo=pytz.utc).astimezone(target_tz)
        to_day = local_time.date()
        if not s_date:
            s_date = to_day
        else:
            s_date = datetime.strptime(s_date, DEFAULT_SERVER_DATE_FORMAT)
            s_date = fields.Date.to_date(s_date)

        if date_type == 'month':
            start_date = s_date.replace(day=1)
            end_date = start_date + relativedelta(months=1) - timedelta(days=1)
        else:
            start_date = s_date - timedelta(days=s_date.weekday())
            end_date = start_date + timedelta(days=6)

        # if end_date > to_day:
        #     end_date = to_day - timedelta(days=1)
        #
        # if start_date == to_day:
        #     s_date = to_day - timedelta(days=1)
        #     start_date = s_date - timedelta(days=s_date.weekday())
        #     end_date = start_date + timedelta(days=6)

        one_date = fields.Datetime.to_datetime(start_date).replace(hour=1, minute=30)
        two_date = fields.Datetime.to_datetime(end_date).replace(hour=1, minute=30) + timedelta(days=1)

        # 前后拉长一个月
        three_date = one_date - relativedelta(months=1)
        four_date = two_date + relativedelta(months=1)
        sql = f"""
            WITH step_sequence AS (SELECT mwo.id,
                              mwo.production_id,
                              mwo.product_id,
                              mwc.s_work_station_id work_station_id,
                              mwo.step,
                              mwo.date_finished,
                              case
                                  when ws.code in ('FQC', 'FQA', 'PKG') then mwo.qty_op_produced_set
                                  else mwo.qty_op_produced_pnl end qty,
                              -- 批次检测
                              CASE
                                  WHEN mwc.work_station_id <> LAG(mwc.work_station_id)
                                                              OVER (PARTITION BY mwo.production_id ORDER BY mwo.step)
                                      OR LAG(mwo.production_id)
                                         OVER (PARTITION BY mwo.production_id ORDER BY mwo.step) IS NULL
                                      THEN 1
                                  ELSE 0
                                  END AS                           is_new_batch
                       FROM mrp_workorder mwo
                                JOIN mrp_workcenter mwc ON mwo.workcenter_id = mwc.id
                                left join work_station ws on mwc.s_work_station_id = ws.id
                                left join mrp_production mp on mp.id = mwo.production_id
                       WHERE mwo.state = 'done'
                         and mp.mo_type != 'W'
                         and mwc.s_not_compute_output != TRUE 
                         and mwo.qty_op_produced > 0
                         and mwo.date_finished >= '{three_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                         and mwo.date_finished < '{four_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                         order by mwo.production_id, mwo.step
                         ),
                     batch_groups AS (SELECT *,
                                             SUM(is_new_batch) OVER (
                                                 PARTITION BY production_id
                                                 ORDER BY step
                                                 ) AS batch_id
                                      FROM step_sequence),
                     batch_summary AS (SELECT work_station_id,
                                              production_id,
                                              batch_id,
                                              MAX(date_finished) AS batch_end_time,
                                              min(qty)           AS total_output
                                       FROM batch_groups
                                       GROUP BY work_station_id, batch_id, production_id),
                     -- 关键部分：自定义日期时段处理
                     daily_production AS (SELECT work_station_id,
                                                 batch_id,
                                                 production_id,
                                                 total_output,
                                                 CASE WHEN ws.code in ('FQC', 'FQA', 'PKG') 
                                                        then round(total_output * setl.uom_value * setw.uom_value / 1000000, 0)
                                                    else round(total_output * pnll.uom_value * pnlw.uom_value / 1000000, 0) end area,
                                                 -- 将时间调整到1点为分界点的日期
                                                 CASE
                                                     -- 当时间在00:00:00到01:29:59之间时，归属前一天
                                                     WHEN batch_end_time::time < '01:30:00'::time 
                                                       THEN DATE(batch_end_time - INTERVAL '1 day')
                                                     -- 当时间在01:30:00到23:59:59之间时，归属当天
                                                     ELSE DATE(batch_end_time)
                                                   END AS production_date
                                          FROM batch_summary
                                          LEFT  JOIN work_station ws on ws.id = batch_summary.work_station_id
                                          LEFT JOIN mrp_production mp on mp.id = batch_summary.production_id
                                          LEFT JOIN product_product pp ON mp.product_id = pp.id
                                            LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                                            LEFT JOIN product_uom_value pnll ON pnll.product_id = pt.id AND pnll.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLL')
                                            LEFT JOIN product_uom_value pnlw ON pnlw.product_id = pt.id AND pnlw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLW')
                                            LEFT JOIN product_uom_value setl ON setl.product_id = pt.id AND setl.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETL')
                                            LEFT JOIN product_uom_value setw ON setw.product_id = pt.id AND setw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETW') 
                                          where batch_end_time >= '{one_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                                            and batch_end_time < '{two_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}')
                SELECT work_station.id,
                       COALESCE(work_station.sequence, 0) sequence,
                       work_station.name,
                       daily_production.production_date,
                       SUM(daily_production.total_output) AS total_output,
                       round(SUM(area), 3) AS total_area
                FROM work_station
                         LEFT JOIN daily_production on daily_production.work_station_id = work_station.id
                where work_station.active is TRUE and work_station.disuse is not true 
                GROUP BY work_station.id, daily_production.production_date, work_station.name, work_station.sequence
                ORDER BY work_station.sequence, daily_production.production_date
        """
        self._cr.execute(sql)
        result = self._cr.fetchall()
        # 创建工站数据映射
        station_data = {}
        for row in result:
            station_id, sequence, name, prod_date, output, area = row
            key = (station_id, sequence, name)
            if key not in station_data:
                station_data[key] = {}
            if prod_date:  # 确保有生产日期
                station_data[key][prod_date] = [output, area]

        # 准备返回数据
        lines = []
        wip = self.env['mrp.workorder.sum'].search([])
        # 按工站ID排序以确保顺序一致
        for (station_id, sequence, name), date_map in sorted(station_data.items(), key=lambda x: x[0][1]):
            i, total_qty, total_area = 0, 0, 0
            daily_output = []
            current_date = start_date
            station = self.env['work.station'].browse(station_id)
            # 生成一周的数据（周一到周日）
            while current_date <= end_date:
                # 获取当天的产量，如果没有则为0
                qty, area = date_map.get(current_date, [0, 0])
                daily_output.append({
                    'qty': qty,
                    'area': int(area),
                    'prod_date': current_date.strftime(DEFAULT_SERVER_DATE_FORMAT)
                })
                total_qty += qty
                total_area += area
                if current_date <= to_day:
                    i += 1
                current_date += timedelta(days=1)
            lines.append({
                'station_id': station_id,
                'sequence': sequence,
                'name': name,
                'daily': daily_output,
                'total_qty': total_qty,
                'wip': sum(wip.filtered(lambda x: x.work_station_id == station).mapped('qty_wip_pnl')) if station.code not in ('FQC', 'FQA', 'PKG') else sum(wip.filtered(lambda x: x.work_station_id == station).mapped('qty_wip_set')),
                'total_area': int(round(total_area, 0)),
                'average': int(round(total_qty / i, 0)) if i else 0,
                'target_daily_production': station.target_daily_production,
                'target_wip': station.target_wip
            })
        return {
            'header': self.get_header(start_date, end_date),  # 调用之前实现的get_header方法
            'lines': lines,
            'dateStr': f'{start_date.strftime(DEFAULT_SERVER_DATE_FORMAT)} to {end_date.strftime(DEFAULT_SERVER_DATE_FORMAT)}'
        }

    @api.model
    def get_header(self, start_date=None, end_date=None):
        header = ['工站', 'WIP']
        week_dates = []
        while True:
            week_dates.append(start_date)
            start_date = start_date + timedelta(days=1)
            if start_date > end_date:
                break

        # 格式化为"月/日"的字符串列表
        week_list = [f"{d.month}/{d.day}" for d in week_dates]

        return header + week_list + ['合计', '平均日产能', '目标日产能']

    @api.model
    def get_scrap_header(self, start_date=None, end_date=None):
        header = ['工站', 'WIP']
        week_dates = []
        while True:
            week_dates.append(start_date)
            start_date = start_date + timedelta(days=1)
            if start_date > end_date:
                break

        # 格式化为"月/日"的字符串列表
        week_list = [f"{d.month}/{d.day}" for d in week_dates]

        return header + week_list + ['合计']

    def get_conditional_date(self, s_date=None, date_type='week'):
        utc_now = datetime.utcnow()
        target_tz = pytz.timezone(self._context.get('tz'))
        local_time = utc_now.replace(tzinfo=pytz.utc).astimezone(target_tz)
        to_day = local_time.date()
        if not s_date:
            s_date = to_day
        else:
            s_date = datetime.strptime(s_date, DEFAULT_SERVER_DATE_FORMAT)
            s_date = fields.Date.to_date(s_date)

        if date_type == 'month':
            start_date = s_date.replace(day=1)
            end_date = start_date + relativedelta(months=1) - timedelta(days=1)
        else:
            start_date = s_date - timedelta(days=s_date.weekday())
            end_date = start_date + timedelta(days=6)

        one_date = fields.Datetime.to_datetime(start_date).replace(hour=1, minute=30)
        two_date = fields.Datetime.to_datetime(end_date).replace(hour=1, minute=30) + timedelta(days=1)

        # 前后拉长一个月
        three_date = one_date - relativedelta(months=1)
        four_date = two_date + relativedelta(months=1)
        # 今天， 准确的开始时间、结束时间    区间开始时间、结束时间
        return to_day, one_date, two_date, three_date, four_date, start_date, end_date

    @api.model
    def get_production_scrap_data(self, s_date=None, date_type='week', product_type='all'):
        product_type_where = ''
        if product_type == 'mass':
            product_type_where = f" and pt.materiel_status = 'P'"
        elif product_type == 'mass':
            product_type_where = f" and pt.materiel_status != 'P'"
        to_day, one_date, two_date, three_date, four_date, start_date, end_date = self.get_conditional_date(s_date, date_type)
        sql = f"""
            WITH step_sequence AS (SELECT mwo.id,
                              mwo.production_id,
                              mwo.product_id,
                              mwc.s_work_station_id work_station_id,
                              mwo.step,
                              mwo.date_finished,
                              case
                                  when ws.code in ('FQC', 'FQA', 'PKG') then mwo.qty_op_produced_set
                                  else mwo.qty_op_produced_pnl end qty,
                              case
                                  when ws.code in ('FQC', 'FQA', 'PKG') then mwo.qty_op_reject_set
                                  else mwo.qty_op_reject end       scrap_qty,
                              CASE
                                  WHEN mwc.work_station_id <> LAG(mwc.work_station_id)
                                                              OVER (PARTITION BY mwo.production_id ORDER BY mwo.step)
                                      OR LAG(mwo.production_id)
                                         OVER (PARTITION BY mwo.production_id ORDER BY mwo.step) IS NULL
                                      THEN 1
                                  ELSE 0
                                  END AS                           is_new_batch
                       FROM mrp_workorder mwo
                                JOIN mrp_workcenter mwc ON mwo.workcenter_id = mwc.id
                                left join work_station ws on mwc.s_work_station_id = ws.id
                       WHERE mwo.state = 'done'
                         and mwc.s_not_compute_output != TRUE
                         and mwo.qty_op_produced > 0
                         and mwo.date_finished >= '{three_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                         and mwo.date_finished < '{four_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                       order by mwo.production_id, mwo.step),
             batch_groups AS (SELECT *,
                                     SUM(is_new_batch) OVER (
                                         PARTITION BY production_id
                                         ORDER BY step
                                         ) AS batch_id
                              FROM step_sequence),
             batch_summary AS (SELECT work_station_id,
                                      production_id,
                                      batch_id,
                                      MAX(date_finished) AS batch_end_time,
                                      min(qty)           AS total_output,
                                      sum(scrap_qty)        total_scrap
                               FROM batch_groups
                               GROUP BY work_station_id, batch_id, production_id),
             -- 关键部分：自定义日期时段处理
             daily_production AS (SELECT work_station_id,
                                         batch_id,
                                         production_id,
                                         CASE
                                             WHEN ws.code in ('FQC', 'FQA', 'PKG')
                                                 then round(total_output * setl.uom_value * setw.uom_value / 1000000, 1)
                                             else round(total_output * pnll.uom_value * pnlw.uom_value / 1000000, 1) end area,
                                         CASE
                                             WHEN ws.code in ('FQC', 'FQA', 'PKG')
                                                 then round(total_scrap * setl.uom_value * setw.uom_value / 1000000, 1)
                                             else round(total_scrap / pnl.uom_value * pnll.uom_value * pnlw.uom_value / 1000000, 1) end scrap_area,
                                         -- 将时间调整到1点为分界点的日期
                                         CASE
                                             -- 当时间在00:00:00到01:29:59之间时，归属前一天
                                             WHEN batch_end_time::time < '01:30:00'::time
                                                 THEN DATE(batch_end_time - INTERVAL '1 day')
                                             -- 当时间在01:30:00到23:59:59之间时，归属当天
                                             ELSE DATE(batch_end_time)
                                             END AS                                                                      production_date
                                  FROM batch_summary
                                           LEFT JOIN work_station ws on ws.id = batch_summary.work_station_id
                                           LEFT JOIN mrp_production mp on mp.id = batch_summary.production_id
                                           LEFT JOIN product_product pp ON mp.product_id = pp.id
                                           LEFT JOIN product_template pt ON pt.id = pp.product_tmpl_id
                                           LEFT JOIN product_uom_value pnll 
                                               ON pnll.product_id = pt.id AND pnll.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLL')
                                           LEFT JOIN product_uom_value pnlw 
                                               ON pnlw.product_id = pt.id AND pnlw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PNLW')
                                           LEFT JOIN product_uom_value setl 
                                               ON setl.product_id = pt.id AND setl.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETL')
                                           LEFT JOIN product_uom_value setw 
                                               ON setw.product_id = pt.id AND setw.uom_id IN (SELECT id FROM uom_uom WHERE code = 'SETW')
                                           LEFT JOIN product_uom_value pnl
                                               on pnl.product_id = pt.id AND pnl.uom_id IN (SELECT id FROM uom_uom WHERE code = 'PANEL')
                                  where batch_end_time >= '{one_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                                    and batch_end_time < '{two_date.strftime(DEFAULT_SERVER_DATETIME_FORMAT)}'
                                    {product_type_where})
            SELECT work_station.id,
                   COALESCE(work_station.sequence, 0) sequence,
                   work_station.name,
                   daily_production.production_date,
                   round(SUM(area), 1)                AS total_area,
                   round(SUM(scrap_area), 1)          AS total_scrap_area
            FROM work_station
                     LEFT JOIN daily_production on daily_production.work_station_id = work_station.id
            where work_station.active is TRUE and work_station.disuse is not true 
            GROUP BY work_station.id, daily_production.production_date, work_station.name, work_station.sequence
            ORDER BY work_station.sequence, daily_production.production_date
        """
        self._cr.execute(sql)
        result = self._cr.fetchall()
        # 创建工站数据映射
        station_data = {}
        for row in result:
            station_id, sequence, name, prod_date, area, scrap_area = row
            key = (station_id, sequence, name)
            if key not in station_data:
                station_data[key] = {}
            if prod_date:  # 确保有生产日期
                station_data[key][prod_date] = [area, scrap_area]

        # 准备返回数据
        lines = []
        wip = self.env['mrp.workorder.sum'].search([])
        # 按工站ID排序以确保顺序一致
        for (station_id, sequence, name), date_map in sorted(station_data.items(), key=lambda x: x[0][1]):
            i, total_area, total_scrap_area = 0, 0, 0
            daily_output = []
            current_date = start_date
            station = self.env['work.station'].browse(station_id)
            # 生成一周的数据（周一到周日）
            while current_date <= end_date:
                # 获取当天的产量，如果没有则为0
                area, scrap_area = date_map.get(current_date, [0, 0])
                daily_output.append({
                    'area': round(area, 1),
                    'scrap_area': round(scrap_area, 1),
                    'prod_date': current_date.strftime(DEFAULT_SERVER_DATE_FORMAT)
                })
                total_scrap_area += scrap_area
                total_area += area
                if current_date <= to_day:
                    i += 1
                current_date += timedelta(days=1)
            lines.append({
                'station_id': station_id,
                'sequence': sequence,
                'name': name,
                'daily': daily_output,
                'total_scrap_area': round(total_scrap_area, 1),
                'wip': sum(
                    wip.filtered(lambda x: x.work_station_id == station).mapped('qty_wip_pnl')) if station.code not in ('FQC', 'FQA', 'PKG') else sum(
                    wip.filtered(lambda x: x.work_station_id == station).mapped('qty_wip_set')),
                'total_area': round(total_area, 1),
                # 'average': int(round(total_qty / i, 0)) if i else 0,
                'target_daily_production': station.target_daily_production
            })
        return {
            'header': self.get_scrap_header(start_date, end_date),  # 调用之前实现的get_header方法
            'lines': lines,
            'dateStr': f'{start_date.strftime(DEFAULT_SERVER_DATE_FORMAT)} to {end_date.strftime(DEFAULT_SERVER_DATE_FORMAT)}'
        }